PixelUp Agency — Lesson 4

Keep It Clean

A new PixelUp staff member has been logging brand deals — but they've been inconsistent with how they enter data. Your job: find the errors, then build validation rules so it can never happen again.

🔍

Your Mission: Error Audit

Before you import the data, look at the table below. Identify the 5 deliberate data errors in the Status and Platform columns. Mark each one — can you spot them all?

Creator	Brand	Deal Value (£)	Status	Platform
@ZaraPlays	Razer	2500	Active	YouTube
@ZaraPlays	G-Fuel	1800	Completed	Youtube
@CoastlineEats	HelloFresh	900	pending	TikTok
@CoastlineEats	Ninja Kitchenware	-150	Active	TikTok
@TechWithTeo	Samsung	3500	Active	YouTube
@TechWithTeo	Squarespace	2200	CANCELLED	YouTube
@StudioKai	Adobe	1200	Active	instagram
@StudioKai	Wacom	800	Completed	Instagram
@RushRunners	Myprotein	2200	Active	YouTube
@RushRunners	Garmin	1500	On Hold	YouTube

Describe the 5 errors you found. Why would each one cause a problem in a formula like =COUNTIF(D2:D11,"Active")?

Task 1

Import the Brand Deal Data

Import the L4 starter.csv into Google Sheets — with its errors intact. Your validation rules will prevent anyone from entering errors like these in the future.

📂

Import starter.csv

1

Create a new Google Sheet

Name it "PixelUp Brand Deals".

2

Import the L4 starter.csv

Go to , upload the L4 starter.csv. Choose Replace current sheet and Comma separator.

3

Check your columns

You should have 7 columns: Creator, Brand, Deal Value, Status, Platform, Date Agreed, Deal Length (days). All 10 rows of brand deal data should be present, including the erroneous ones.

4

Format the Deal Value column as currency

Select column C (Deal Value). Apply .

⚠️ Don't fix the errors yet! Leave the bad data as-is for now. You'll fix it after adding validation — that way you can test that your rules correctly reject the bad values.

Task 2

Status Column Validation

Add a dropdown list so only the four valid status values can ever be entered in the Status column.

📋

Add dropdown validation to Status (column D)

1

Select the Status cells

Click on cell D2, then shift-click D11 to select all 10 status cells (not the header).

2

Open Data Validation

Go to . A sidebar opens on the right.

3

Add a rule

Click + Add rule. Under "Criteria", open the dropdown and choose "Dropdown".

4

Enter the allowed values

Type each value into a separate field: Active / Pending / Completed / Cancelled. Use the exact capitalisation shown here.

5

Set to reject invalid input

Under "If the data is invalid", choose "Reject the input". Click Done.

6

Test it

Try clicking on a Status cell and typing pending (lowercase). It should be rejected with a red error message. Now try choosing from the dropdown — this should work fine.

💡 The existing bad data: After adding validation, the cells that already contain "pending", "CANCELLED", and "On Hold" will show a red triangle warning icon — they're flagged as invalid. You'll fix these in Task 7.

Task 3

Platform Column Validation

Repeat the process for the Platform column — only "YouTube", "TikTok", and "Instagram" are valid platforms for PixelUp's current clients.

📋

Add dropdown validation to Platform (column E)

1

Select the Platform cells

Select cells E2:E11 (all Platform values, not the header).

2

Add a dropdown rule with three values

Go to → Add rule → Dropdown.
Enter: YouTube / TikTok / Instagram
Set to Reject the input. Click Done.

3

Test it

Try typing youtube (all lowercase) in a Platform cell — it should be rejected. Try Youtube (capital Y only) — also rejected. Only the exact spelling "YouTube" should be accepted.

Case sensitivity: "YouTube" and "Youtube" are different strings. This is why "Youtube" and "instagram" in the starter data are errors — they don't exactly match the approved dropdown values.

Task 4

Deal Value Validation

No deal can have a negative value. Add a number range check to the Deal Value column.

🔢

Add number validation to Deal Value (column C)

1

Select the Deal Value cells

Select C2:C11.

2

Add a number rule

Go to → Add rule. Under Criteria, choose "Greater than". Enter 0 in the value field.

3

Add a helpful error message

Expand the "Advanced options" section (if available) and add an error message: "Deal value must be greater than £0. Check the contract before entering." Choose Reject the input. Click Done.

4

Test it

Try entering -150 in a Deal Value cell — it should be rejected. Try entering 0 — also rejected (the rule is "greater than", not "greater than or equal to"). Try 1 — accepted.

Tasks 5 & 6

Conditional Formatting

Colour-code the Status column and highlight high-value deals so PixelUp can assess the deal sheet at a glance.

🎨

Task 5 — Colour-code Status values

1

Select the Status column (D2:D11)

You'll add three separate formatting rules — one per valid status value.

2

Add rule: Active → Green

Go to . Under "Format rules", choose "Text is exactly", enter Active. Set the fill colour to green. Click Add another rule.

3

Add rule: Pending → Amber

Same process: "Text is exactly" → Pending → amber/yellow fill. Click Add another rule.

4

Add rule: Cancelled → Red

"Text is exactly" → Cancelled → red fill. Click Done.

💜

Task 6 — Highlight High-Value Deals

5

Select the Deal Value column (C2:C11)

This is where you'll highlight deals worth over £2,000.

6

Add a "Greater than" rule

Go to . Under "Format rules", choose "Greater than", enter 2000. Set the fill to a light purple and the text to bold. Click Done.

✅ Check: Samsung (£3,500), TechWithTeo Squarespace (£2,200), Razer (£2,500), and Garmin (£2,200 — wait, that's @RushRunners/Myprotein at £2,200 and Garmin at £1,500) should be highlighted. Count which deals exceed £2,000.

Task 7

Fix the Bad Data

Now that your validation is in place, fix the 5 erroneous entries using the validated dropdowns.

🔧

Correct each error

Creator / Brand	Column	Error	Correct Value
@ZaraPlays / G-Fuel	Platform	Youtube	YouTube
@CoastlineEats / HelloFresh	Status	pending	Pending
@CoastlineEats / Ninja Kitchenware	Deal Value	-150	Delete this row — the deal is invalid. Ask your teacher what to enter instead.
@TechWithTeo / Squarespace	Status	CANCELLED	Cancelled
@StudioKai / Adobe	Platform	instagram	Instagram
@RushRunners / Garmin	Status	On Hold	Pending (nearest valid option — but note: this information is lost!)

⚠️ The "On Hold" problem: Our validation only allows 4 statuses. "On Hold" is a valid business concept but isn't in our list. To fix this properly, we'd need to add "On Hold" to the dropdown — this shows why validation rules need to be designed with input from the people who actually use the data.

After fixing all the errors, how many deals now show as "Active"? Does your green conditional formatting update automatically?

Extension Task

Going Further

Extension

Deal Length Validation

The Deal Length column (days) should only accept whole numbers between 7 and 365 — no deal is shorter than a week or longer than a year.

Select the Deal Length column (G2:G11)
Go to Data → Data validation → Add rule
Choose "Is between" from the Criteria dropdown
Enter 7 and 365 as the minimum and maximum values
Set to "Reject the input" with an error message
Test it: the @TechWithTeo / Squarespace deal has a Deal Length of 0 — is this flagged?

Extension

COUNTIF Formula

Add a summary section below your data that counts how many deals have each status. Use COUNTIF — which counts cells that match a condition.

In a cell below your data, type: =COUNTIF(D2:D11,"Active")
Add similar formulas for Pending, Completed, and Cancelled
Why would "pending" (lowercase) return 0 even if there were lowercase entries? This reinforces why case-sensitive validation matters.

✅

Lesson 4 Complete

Data Quality — Secured!

Your spreadsheet now prevents bad data at the point of entry. Next lesson: turning all this data into a professional client dashboard with charts.

✅

What you learned today

Why data validation is essential — inconsistent data breaks formulas and reports
Five types of validation: list, range, type, length, and presence checks
How to add dropdown list validation in Google Sheets (Data → Data validation)
How to add number range validation (greater than, between)
How to use conditional formatting to colour-code data automatically (Format → Conditional formatting)
The limitation: validation limits reasonable values, not correct ones

Coming up in L5: PixelUp is pitching to investors. We need a professional visual dashboard — bar charts, line charts, and the Google Sheets-exclusive SPARKLINE function. The data we've cleaned this lesson will power the charts in the next.